package com.exam.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.exam.entity.Classes;
import com.exam.entity.Exam;
import com.exam.entity.ExamRecord;
import com.exam.entity.User;
import com.exam.entity.UserRecord;
import com.exam.util.DBUtil;


public class ExamRecordDao {
	
	//班级查询
	public List<Classes> sel() throws SQLException, ParseException {
		Connection conn=DBUtil.getConnection();
		List<Classes> list=new ArrayList<Classes>();
		String sql="select * from classes";
		QueryRunner runner = new QueryRunner();
		list =runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
		conn.close(); 
		return list;
	}
	
	//查询考试统计
	public List<ExamRecord> sel(int page) throws SQLException, ParseException {
		Connection conn=DBUtil.getConnection();
		int temp  = (page-1)*10;
		List<ExamRecord> list=new ArrayList<ExamRecord>();
		String sql="select * from exam_record limit ?,10";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[1];
		param[0]=temp;
		list =runner.query(conn,sql,param,new BeanListHandler<ExamRecord>(ExamRecord.class));
		conn.close(); 
		return list;
	}
	
	//考试创建
	public int registerRecord(ExamRecord record) throws SQLException {
        Connection conn=DBUtil.getConnection();
        String sql="insert into exam_record(exam_id, record_name, record_start, record_end,record_score) values (?,?,?,?,?)";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[5];
		param[0]=record.getExam_id();
		param[1]=record.getRecord_name();
		param[2]=record.getRecord_start();
		param[3]=record.getRecord_end();
		param[4]=record.getRecord_score();
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	//加入考试班级
	public int addclass(int recordid,int classid) throws SQLException {
        Connection conn=DBUtil.getConnection();
        String sql="insert into class_record(record_id,class_id) values (?,?)";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[2];
		param[0]=recordid;
		param[1]=classid;
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	//查询加入考试班级的学生
	public List<User> sell(int class_id) throws SQLException, ParseException {
		Connection conn=DBUtil.getConnection();
		List<User> list=new ArrayList<User>();
		String sql="select * from user where class_id=?";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[1];
		param[0]=class_id;
		list =runner.query(conn,sql,param,new BeanListHandler<User>(User.class));
		conn.close(); 
		return list;
	}
	//加入考试用户
	public int addUserRecord(int user_id,int recordid,int classid) throws SQLException {
        Connection conn=DBUtil.getConnection();
        String sql="insert into user_record(user_id,record_id,class_id) values (?,?,?)";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[3];
		param[0]=user_id;
		param[1]=recordid;
		param[2]=classid;
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	
	//通过examid查exam名
	public String selExamName(int exam_id ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT exam_name FROM exam WHERE exam_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(exam_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		String exam_name=null;
		while(rs.next()){
			exam_name=rs.getString(1);
		}		
		return exam_name;
	}
	//查所有试卷
	public List selExam() throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT * FROM exam";
		QueryRunner runner = new QueryRunner();
		List list =runner.query(conn,sql,new BeanListHandler<Exam>(Exam.class));
		conn.close(); 
		return list;
	}
	//通过examid查exam总分
	public int selExamScore(int exam_id ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT exam_score FROM exam WHERE exam_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(exam_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		int exam_score=0;
		while(rs.next()){
			exam_score=rs.getInt(1);
		}		
		return exam_score;
	}
	
	//通过recordid查 此条考试记录
	public ExamRecord selRecord(int recordid ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT * FROM exam_record where record_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(recordid);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		ExamRecord record = null;
		while(rs.next()){
			int record_id=rs.getInt(1);
			int exam_id=rs.getInt(2);
			String record_name=rs.getString(3);
			String record_start=rs.getString(4);
			String record_end=rs.getString(5);
			int record_scors=rs.getInt(6);
			record=new ExamRecord(record_id, exam_id, record_name, record_start, record_end, record_scors);
		}
		conn.close();
		return record;
	}
	
	//通过userid查user名
	public String selUsername(int user_id ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT user_name FROM user WHERE user_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(user_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		String user_name=null;
		while(rs.next()){
			user_name=rs.getString(1);
		}		
		return user_name;
	}
	
	//查出所有用户 --要分页查询
	public List show(int page,int id) throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		int temp  = (page-1)*10;
		String sql="select * from user_record where record_id =? limit ?,10";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[2];
		param[0]=id;
		param[1]=temp;
		List list =runner.query(conn,sql,param,new BeanListHandler<UserRecord>(UserRecord.class));
		conn.close(); 
		return list;
	}
	//成绩排名
	public List grage(int recordid,int classid) throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		String sql="select * from user_record where record_id=? and class_id =? ORDER BY user_score DESC";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[2];
		param[0]=recordid;
		param[1]=classid;
		List list =runner.query(conn,sql,param,new BeanListHandler<UserRecord>(UserRecord.class));
		conn.close(); 
		return list;
	}
	//查询班级成绩
	public List grade(int recordid,int classid) throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		String sql="select * from user_record where record_id=? and class_id =?";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[2];
		param[0]=recordid;
		param[1]=classid;
		List list =runner.query(conn,sql,param,new BeanListHandler<UserRecord>(UserRecord.class));
		conn.close(); 
		return list;
	}
	//查班级信息
	public List selClass() throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		String sql="select * from classes";
		QueryRunner runner = new QueryRunner();
		List list =runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
		conn.close(); 
		return list;
	}
	//查班级名称
	public String selClass(int class_id) throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		String sql="select * from classes where class_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(class_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		String class_class=null;
		while(rs.next()){
			class_class=rs.getString(2);
		}		
		return class_class;
	}	
	// 学生的总人数
	public int count(int recordid) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="select count(*) from user_record where record_id =?";
		QueryRunner runner = new QueryRunner();
		Long n  = runner.query(conn, sql,recordid, new ScalarHandler<Long>());
		conn.close(); 
		return n.intValue();
	}
	
	// 考试统计总数
	public int count() throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="select count(*) from exam_record";
		QueryRunner runner = new QueryRunner();
		Long n  = runner.query(conn, sql, new ScalarHandler<Long>());
		conn.close(); 
		return n.intValue();
	}
	//通过id删除userrecord记录
	public int delUserRecord(int id) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="delete from user_record where record_id =?";	 
		QueryRunner runner = new QueryRunner();
		int n = runner.update(conn, sql, id);
		conn.close();
		return n;
	}
	//通过id删除classrecord记录
	public int delClassRecord(int id ) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="delete from class_record where record_id =?";	 
		QueryRunner runner = new QueryRunner();
		int n = runner.update(conn, sql, id);
		conn.close();
		return n;
	}
	//通过id删除record记录
	public int del(int id ) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="delete from exam_record where record_id =?";	 
		QueryRunner runner = new QueryRunner();
		int n = runner.update(conn, sql, id);
		conn.close();
		return n;
	}
	//通过id删除userrecord记录
	public int del1(int id) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="delete from user_record where user_id =?";	 
		QueryRunner runner = new QueryRunner();
		int n = runner.update(conn, sql, id);
		conn.close();
		return n;
	}
}
